package cn.codewen.shop.dao.impl;

import cn.codewen.shop.dao.CategoryDao;
import cn.codewen.shop.pojo.Category;
import cn.codewen.shop.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CategoryDaoImpl implements CategoryDao {
    @Override
    public List<Category> findAll() {
        //0. 声明一个List
        List<Category> categories = new ArrayList<>();
        try {

            //1. 得到连接
            Connection conn = JDBCUtils.getConnection();
            //2. 定义SQL语句
            String sql = "select * from category";
            //3. 创建stm对象
            Statement stm = conn.createStatement();
            //4. 执行sql语句
            ResultSet rs = stm.executeQuery(sql);
            //5. 遍历取出数据
            while (rs.next()) {
                //5.1 创建一个 category 对象
                Category category = new Category();
                //5.2 给category对象赋值
                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                category.setStatus(rs.getInt("status"));
                category.setSort(rs.getInt("sort"));
                //5.3 把category追加到categories List中
                categories.add(category);
            }
            //6 关闭相关对象
            JDBCUtils.close(rs, stm, conn);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //System.out.println(categories);
        //7. 返回数据
        return categories;

    }

    @Override
    public void insert(Category category) {
        try {
            //1. 得到连接
            Connection conn = JDBCUtils.getConnection();
            //2. 声明SQL语句
            String sql = "insert into category(name, status, sort) VALUES ('" + category.getName() + "'," + category.getStatus() + "," + category.getSort() + ")";
            //3. 创建stm对象

            Statement stm = conn.createStatement();

            //4. stm执行SQL
            stm.executeUpdate(sql);
            //5. 关闭
            JDBCUtils.close(null, stm, conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    @Override
    public void save(Category category) {
        try {
            //1. 得到连接
            Connection conn = JDBCUtils.getConnection();
            //2. 声明SQL语句
            String sql = "update category set name='" + category.getName() + "',status=" + category.getStatus() + ",sort=" + category.getSort() + " where id=" + category.getId();
            //3. 创建stm对象

            Statement stm = conn.createStatement();

            //4. stm执行SQL
            stm.executeUpdate(sql);
            //5. 关闭
            JDBCUtils.close(null, stm, conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public Category getCateById(int id) {
        //0. 声明一个Category对象
        Category category = null;
        try {

            //1. 得到连接
            Connection conn = JDBCUtils.getConnection();
            //2. 定义SQL语句
            String sql = "select * from category where id=" + id;
            //3. 创建stm对象
            Statement stm = conn.createStatement();
            //4. 执行sql语句
            ResultSet rs = stm.executeQuery(sql);
            //5. 遍历取出数据
            if (rs.next()) {
                //5.1 创建一个 category 对象
                category = new Category();
                //5.2 给category对象赋值
                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                category.setStatus(rs.getInt("status"));
                category.setSort(rs.getInt("sort"));
            }
            //6 关闭相关对象
            JDBCUtils.close(rs, stm, conn);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //7. 返回数据
        return category;
    }

    @Override
    public void delete(int id) {
        try {
            //1. 得到连接
            Connection conn = JDBCUtils.getConnection();
            //2. 声明SQL语句
            String sql = "delete from category where id=" + id;
            //3. 创建stm对象

            Statement stm = conn.createStatement();

            //4. stm执行SQL
            stm.executeUpdate(sql);
            //5. 关闭
            JDBCUtils.close(null, stm, conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}
